package com.deng.maintenance.dao.impl;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.deng.common.SQLHelper;
import com.deng.maintenance.dao.IRuleEdit;
import com.deng.maintenance.vo.OrganizationIndicateVo;
import com.deng.maintenance.vo.RuleGroupVo;
import com.deng.maintenance.vo.RuleVo;
import com.deng.performance.vo.OrganizationFirstBonusVo;

public class RuleEditImpl extends SQLHelper implements IRuleEdit {

	
	
	public List<RuleVo> getRuleByGroup(int organization_id, String group_id) {
//UNIT:				group_id,is_mark,unit_id,type_name,value,organization_id
		try{
			Connection conn = getConnect();
			ResultSet rs = queryData(conn,"select * from UNIT_PRICE_RULE_UNIT	" +
					"	where organization_id = "+organization_id +//UNIT_PRICE_RULE_UNIT
					"	and group_id = '"+group_id+"'" +
					"	order by unit_id");
			List<RuleVo> list = new ArrayList<RuleVo>();
			while(rs.next()){
				RuleVo vo = new RuleVo();
				vo.group_id = rs.getString("group_id");
				vo.type_name = rs.getString("type_name");
				vo.organization_id = rs.getInt("organization_id");
				vo.value = rs.getString("value");
				vo.unit_id = rs.getInt("unit_id");
				vo.is_mark = rs.getInt("is_mark");
				vo.fan = rs.getInt("fan");
				if(vo.is_mark == 1){
					vo.name = vo.value;
				}else{
					if(vo.type_name.equals("org_operate_organization_id")){
						vo.name = "执行科室= "+vo.value;
					}else if(vo.type_name.equals("org_order_organization_id")){
						vo.name = "开单科室= "+vo.value;
					}else if(vo.type_name.equals("org_item_id")){
						vo.name = "项目编码= "+vo.value;
					}else if(vo.type_name.equals("org_item_name")){
						vo.name = "项目名称= "+vo.value;
					}else if(vo.type_name.equals("org_fee_type_id")){
						vo.name = "费用类别编码 = "+vo.value;
					}else if(vo.type_name.equals("and")){
						vo.name = "并且";
					}else if(vo.type_name.equals("or")){
						vo.name = "或者";
					}
					else{
						vo.name = vo.value;
					}
				}
				list.add(vo);
			}
			freeConnection(conn);
			return list;
		}catch(Exception ex){
			ex.printStackTrace();
		}
		return null;
	}

	public List<RuleGroupVo> getAllRuleGroup(int organization_id,Connection conn) {
		try{
//GROUP:			organization_id,name,group_id,is_mark
			System.out.println("..........................");
			if(conn == null)
			{
				conn = getConnect();
			}
			ResultSet rs = queryData(conn,"select * from UNIT_PRICE_RULE_GROUP	" +
					"	where organization_id = "+organization_id +//UNIT_PRICE_RULE_UNIT
					"	order by group_id");
			List<RuleGroupVo> list = new ArrayList<RuleGroupVo>();
			while(rs.next()){
				RuleGroupVo vo = new RuleGroupVo();
				vo.group_id = rs.getString("group_id");
				vo.name = rs.getString("name");
				vo.organization_id = rs.getInt("organization_id");
				vo.is_mark = rs.getInt("is_mark");
				vo.is_fee = rs.getInt("is_fee");
				vo.order_operate = rs.getInt("order_operate");
				list.add(vo);
			}
			freeConnection(conn);
			return list;
		}catch(Exception ex){
			ex.printStackTrace();
		}
		return null;
	}

	public void addRuleGroup(List<RuleGroupVo> groupList) {
		// TODO Auto-generated method stub
		
	}

	public void addRuleUnit(List<RuleVo> ruleList) {
		// TODO Auto-generated method stub
		
	}


	public void addOrUpdateRuleGroup(RuleGroupVo gVo) {
//UNIT:				group_id,is_mark,unit_id,type_name,value,organization_id
		try{
			Connection conn = getConnect();
//			int newMaxId = getNewMaxGroupId(gVo.organization_id);
			if(gVo.is_update == 0){//插入
				System.out.println("插入");
				String sql = "insert into UNIT_PRICE_RULE_GROUP" +
						"	(organization_id,name,group_id,is_mark,order_operate,is_fee)" +
						"	values("+gVo.organization_id+",'"+gVo.name+"','"+gVo.group_id+"',"+gVo.is_mark+","+gVo.order_operate+","+gVo.is_fee+")";
				updateData(conn,sql);
				if(gVo.is_mark == 0){//
					addRule(gVo.organization_id,gVo.group_id,gVo.ruleArr);
				}
			}else{//更新
				String sql = "update UNIT_PRICE_RULE_GROUP" +
					" set order_operate = "+gVo.order_operate+"," +
					" is_fee = " +gVo.is_fee+","+
					" name = '" +gVo.name+"'"+
					" where organization_id = " +gVo.organization_id+
					" and group_id = '"+gVo.group_id+"'";
				updateData(conn,sql);
				System.out.println("更新");
				deleteRule(gVo.organization_id,gVo.group_id);
				addRule(gVo.organization_id,gVo.group_id,gVo.ruleArr);
			}
			freeConnection(conn);
		}catch(Exception ex){
			ex.printStackTrace();
		}
	}
	
	public void addRule(int organization_id, String group_id, List<RuleVo> ruleList) {
		try{
			Connection conn = getConnect();
			String sql = "";
			for(int i =0;i< ruleList.size();i++){
				RuleVo vo = ruleList.get(i);
				sql = "insert into UNIT_PRICE_RULE_UNIT" +
					"	(organization_id,group_id,unit_id,type_name,value,is_mark,fan)" +
					"	values("+organization_id+",'"+group_id+"',"+(i+1)+",'"+vo.type_name+"','"+vo.value+"',"+vo.is_mark+","+vo.fan+")";
				updateData(conn,sql);
			}
			freeConnection(conn);
		}catch(Exception ex){
			ex.printStackTrace();
		}
	}
	public void deleteRule(int organization_id,String group_id){
		Connection conn = getConnect();
		String sql = "delete UNIT_PRICE_RULE_UNIT" +
				"	where organization_id = " +organization_id+
				"	and group_id = '"+group_id+"'";
		updateData(conn,sql);
		freeConnection(conn);
	}
	private int getNewMaxGroupId(int organization_id) {
		try{
			Connection conn = getConnect();
			ResultSet rs = queryData(conn,"select max(group_id) as maxId from UNIT_PRICE_RULE_GROUP" +
					"	where organization_id = "+organization_id);
			if(rs.next()){
				return rs.getInt("maxId")+1;
			}
		}catch(Exception ex){
			ex.printStackTrace();
		}
		return 1;
	}
	
	private int getNewMaxUnitId(int organization_id,int group_id) {
		try{
			Connection conn = getConnect();
			ResultSet rs = queryData(conn,"select max(group_id) as maxId from UNIT_PRICE_RULE_GROUP" +
					"	where organization_id = "+organization_id);
			if(rs.next()){
				freeConnection(conn);
				return rs.getInt("maxId")+1;
			}
			
		}catch(Exception ex){
			ex.printStackTrace();
		}
		return 1;
	}

	public void switchRule(RuleVo gva, RuleVo gvb) {
		// TODO Auto-generated method stub
		String aGid = gva.group_id;
		String bGid = gvb.group_id;
		String tGid = "";
		updateUnitId(gva,aGid,tGid);
		updateUnitId(gvb,bGid,aGid);
		updateUnitId(gva,tGid,bGid);
	}
	private void updateUnitId(RuleVo gv,String oldId,String newId){
		try{
			Connection conn = getConnect();		
			updateData(conn,"update UNIT_PRICE_RULE_UNIT set group_id = '"+newId+"'" +
					"	where organization_id = "+gv.organization_id+
					"	and group_id = '"+oldId+"'");		
			freeConnection(conn);
		}catch(Exception ex){
			ex.printStackTrace();
		}
	}

	public void switchRuleGroup(RuleGroupVo gva, RuleGroupVo gvb) {
		String aGid = gva.group_id;
		String bGid = gvb.group_id;
		String tGid = "";
		updateGroupId(gva,aGid,tGid);
		updateGroupId(gvb,bGid,aGid);
		updateGroupId(gva,tGid,bGid);
	}
	private void updateGroupId(RuleGroupVo gv,String oldId,String newId){
		try{
			Connection conn = getConnect();
		
			updateData(conn,"update UNIT_PRICE_RULE_GROUP set group_id = '"+newId+"'" +
					"	where organization_id = "+gv.organization_id+
					"	and group_id = '"+oldId+"'");		
			if(gv.is_mark == 0){
				updateData(conn,"update UNIT_PRICE_RULE_UNIT set group_id = '"+newId+"'" +
						"	where organization_id = "+gv.organization_id+
						"	and group_id = '"+oldId+"'");
			}		
			freeConnection(conn);
		}catch(Exception ex){
			ex.printStackTrace();
		}
	}

	public void deleteGroup(RuleGroupVo vo) {
		try{
			Connection conn = getConnect();
			updateData(conn,"delete UNIT_PRICE_RULE_GROUP where group_id = '"+vo.group_id +"'"+
					"	and organization_id = "+vo.organization_id);				
			if(vo.is_mark == 0){
				updateData(conn,"delete UNIT_PRICE_RULE_UNIT where group_id = '"+vo.group_id +"'"+
						"	and organization_id = "+vo.organization_id);			
			}		
			freeConnection(conn);
		}catch(Exception ex){
			ex.printStackTrace();
		}
	}

	public BigDecimal testUnitCount(int organization_id,int duty_id,Date stat_time,String start_time,String end_time) {
		return null;
	}

	public void saveSP(int organization_id,int is_fee,int key) {
//		if(is_fee == 1){
//			saveSP_Fee(organization_id,key);
//		}else{
			saveSP_Count(organization_id,key);
//		}
	}
	
//	public void saveSP_Fee(int organization_id,int key) {
//		try{
//			Connection conn = getConnect();
//			String sql = "select * from sysObjects where name = 'UP_Period_"+organization_id+"'";
//			StringBuffer sqlF = new StringBuffer();
//			ResultSet rsrs = queryData(conn,sql);
//			if(rsrs.next()){
//				sqlF.append("ALTER ");
//			}else{
//				sqlF.append("CREATE ");
//			}
//			sqlF.append("	FUNCTION UP_Period_"+organization_id+
//						"\r("+
//						"\r    @organization_id int,@duty_id int,@stat_time datetime,@start_time nvarchar(10),@end_time nvarchar(10)"+
//						"\r)" +
//						"\rRETURNS decimal(30,4)" +
//						"\rAS" +
//						"\rBEGIN" +
//						"\rdeclare @rt decimal(18,2)" +
//						"\r    set @rt = ");
//			ResultSet rs = queryData(conn,"select g.organization_id,g.name," +
//					"	g.group_id as gGid,u.group_id as uGid," +
//					"	g.is_mark as gMark,u.is_mark as uMark," +
//					"	u.unit_id,u.type_name,u.value  from UNIT_PRICE_RULE_GROUP as g" +
//					"	left join UNIT_PRICE_RULE_UNIT as u" +
//					"	on g.organization_id = u.organization_id" +
//					"	and g.group_id = u.group_id" +
//					"	where g.organization_id = "+organization_id+
//					"	order by gGid,unit_id");
//			
//			List<RuleGroupVo> l = new ArrayList<RuleGroupVo>();
//			while(rs.next()){
//				String name = rs.getString("name");
//				int gGid = rs.getInt("gGid");
//				int uGid = rs.getInt("uGid");
//				int gMark = rs.getInt("gMark");
//				int uMark = rs.getInt("uMark");
//				int unit_id = rs.getInt("unit_id");
//				String type_name = rs.getString("type_name");
//				String value = rs.getString("value");
//				
//				if(gGid != l.size()){
//					RuleGroupVo gVo = new RuleGroupVo();
//					gVo.name = name;
//					gVo.group_id = gGid;
//					gVo.is_mark = gMark;
//					
//					List<RuleVo> l2 = new ArrayList<RuleVo>();
//					
//					
//					RuleVo vo = new RuleVo();
//					vo.is_mark = uMark;
//					vo.group_id = gGid;
//					vo.type_name = type_name;
//					vo.value = value;
//					vo.unit_id = unit_id;
//					l2.add(vo);
//					
//					gVo.ruleArr = l2;
//					l.add(gVo);
//				}else{
//					RuleVo vo = new RuleVo();
//					vo.is_mark = uMark;
//					vo.group_id = gGid;
//					vo.type_name = type_name;
//					vo.value = value;
//					vo.unit_id = unit_id;
//					
//					l.get(gGid-1).ruleArr.add(vo);
//				}
//			}
//			
//			for(int i =0;i< l.size();i++){
//				RuleGroupVo gVo = l.get(i);
//				if(gVo.is_mark == 0){
//					for(int j = 0;j < gVo.ruleArr.size();j++){
//						RuleVo vo = gVo.ruleArr.get(j);
//						if(j == 0){
//							sqlF.append(
//									"\r    (" +
//									"\r        select case when sum(fee) is null then 0 else sum(fee) end" +
//									"\r        as count from t_org_organization_item_count_month" +
//									"\r        where ");
//						}
//						
//						if(vo.is_mark == 1){
//							sqlF.append("\r        "+vo.value);
//						}else{
//							if(vo.type_name.equals("org_order_organization_id")){
//								sqlF.append("\r        org_order_organization_id in " +
//										"\r        (select original_id from t_sys_stat_department_vs_original_department where stat_id " +
//										"\r        in("+vo.value.substring(0, vo.value.length()-1)+"))");
//							}else if(vo.type_name.equals("org_operate_organization_id")){
//								sqlF.append("\r        org_operate_organization_id in (select original_id " +
//										"\r       from t_sys_stat_department_vs_original_department" +
//										"\r       where stat_id in("+vo.value.substring(0, vo.value.length()-1)+"))");
//							}else if(vo.type_name.equals("org_item_id")){
//								String[] strArr = vo.value.split(",");
//								sqlF.append("\r        org_item_id in (");
//								for(int k =0;k< strArr.length ;k++){
//									sqlF.append("'"+strArr[k]+"',");
//								}
//								sqlF.delete(sqlF.length()-1, sqlF.length());
//								sqlF.append(")");
//							}else if(vo.type_name.equals("org_item_name")){
//								String[] strArr = vo.value.split(",");
//								sqlF.append("\r        org_item_name in (");
//								for(int k =0;k< strArr.length ;k++){
//									sqlF.append("'"+strArr[k]+"',");
//								}
//								sqlF.delete(sqlF.length()-1, sqlF.length());
//								sqlF.append(")");
//							}else if(vo.type_name.equals("org_fee_type_id")){
//								String[] strArr = vo.value.split(",");
//								sqlF.append("\r        org_fee_type_id in (");
//								for(int k =0;k< strArr.length ;k++){
//									sqlF.append("'"+strArr[k]+"',");
//								}
//								sqlF.delete(sqlF.length()-1, sqlF.length());
//								sqlF.append(")");
//							}else if(vo.type_name.equals("sjly")){
//								if(vo.value == null || vo.value.equals("")){
//									System.out.println("vo.value == null");
//								}else if(vo.value.equals("0")){
//									sqlF.append("\r        sjly=0");
//								}else if(vo.value.equals("1")){
//									sqlF.append("\r        sjly=1");
//								}
//							}
//						}
//						
//						
//						if(j == gVo.ruleArr.size()-1){
//							sqlF.append(
//								"\r    and stat_time >= dbo.GetPeriodDate(@stat_time,@start_time)" +
//								"\r    and stat_time <  dbo.GetPeriodDate(@stat_time,@end_time)" +
//								"\r    )" );
//						}	
//					}
//				}else{
//					sqlF.append("\r        "+gVo.name);
//				}
//			}
//			sqlF.append("\r    return @rt;");
//			sqlF.append("\rEND");
//			System.out.println(sqlF.toString());
//			updateData(conn,sqlF.toString());
//			freeConnection(conn);
//		}catch(Exception ex){
//			ex.printStackTrace();
//		}		
//	
//	
//	}
	
	public void saveSP_Count(int organization_id,int key) {

		try{
			Connection conn = getConnect();
			saveSPForCopy(organization_id, key, conn);			
			freeConnection(conn);
		}catch(Exception ex){
			ex.printStackTrace();
		}		
	
	}
	
	public void saveSPForCopy(int organization_id,int key,	Connection conn) {

		try{
			String sql = "select * from sysObjects where name = 'UP_Period_"+organization_id+"'";
			StringBuffer sqlF = new StringBuffer();
			ResultSet rsrs = queryData(conn,sql);
			if(rsrs.next()){
				sqlF.append("ALTER ");
			}else{
				sqlF.append("CREATE ");
			}
			sqlF.append("	FUNCTION UP_Period_"+organization_id+
						"\r("+
						"\r    @organization_id int,@duty_id int,@stat_time datetime,@start_time nvarchar(10),@end_time nvarchar(10)"+
						"\r)" +
						"\rRETURNS decimal(30,4)" +
						"\rAS" +
						"\rBEGIN" +
						"\rdeclare @rt decimal(18,2)" +
						"\r    set @rt = ");
			ResultSet rs = queryData(conn,"select g.organization_id,g.is_fee,g.name," +
					"	g.group_id as gGid,u.group_id as uGid," +
					"	g.is_mark as gMark,u.is_mark as uMark,u.fan as fan," +
					"	u.unit_id,u.type_name,u.value,g.order_operate" +
					"   from UNIT_PRICE_RULE_GROUP as g" +
					"	left join UNIT_PRICE_RULE_UNIT as u" +
					"	on g.organization_id = u.organization_id" +
					"	and g.group_id = u.group_id" +
					"	where g.organization_id = "+organization_id+
					"	order by gGid,unit_id");
			
			List<RuleGroupVo> l = new ArrayList<RuleGroupVo>();
			String tmpGid="";
			while(rs.next()){
				String name = rs.getString("name");
				String gGid = rs.getString("gGid");
				String uGid = rs.getString("uGid");
				int gMark = rs.getInt("gMark");
				int uMark = rs.getInt("uMark");
				int unit_id = rs.getInt("unit_id");
				int order_operate = rs.getInt("order_operate");
				int is_fee = rs.getInt("is_fee");
				int fan = rs.getInt("fan");
				String type_name = rs.getString("type_name");
				String value = rs.getString("value");
				
				if(!tmpGid.equals(gGid))
				{
					tmpGid = gGid;
					RuleGroupVo gVo = new RuleGroupVo();
					gVo.name = name;
					gVo.group_id = gGid;
					gVo.is_mark = gMark;
					gVo.order_operate = order_operate;
					gVo.is_fee = is_fee;
					List<RuleVo> l2 = new ArrayList<RuleVo>();
					
					
					RuleVo vo = new RuleVo();
					vo.is_mark = uMark;
					vo.group_id = gGid;
					vo.type_name = type_name;
					vo.value = value;
					vo.unit_id = unit_id;
					vo.fan = fan;
					l2.add(vo);
					
					gVo.ruleArr = l2;
					l.add(gVo);
				}
				else
				{
					RuleVo vo = new RuleVo();
					vo.is_mark = uMark;
					vo.group_id = gGid;
					vo.type_name = type_name;
					vo.value = value;
					vo.unit_id = unit_id;
					vo.fan = fan;
					for(int i = 0;i<l.size();i++)
					{
						RuleGroupVo tmpRg = l.get(i);
						if(tmpRg.group_id.equals(gGid))
						{
							tmpRg.ruleArr.add(vo);
							break;
						}
					}
					
				}
				
//				if(gGid != l.size()){
//					RuleGroupVo gVo = new RuleGroupVo();
//					gVo.name = name;
//					gVo.group_id = gGid;
//					gVo.is_mark = gMark;
//					gVo.order_operate = order_operate;
//					gVo.is_fee = is_fee;
//					List<RuleVo> l2 = new ArrayList<RuleVo>();
//					
//					
//					RuleVo vo = new RuleVo();
//					vo.is_mark = uMark;
//					vo.group_id = gGid;
//					vo.type_name = type_name;
//					vo.value = value;
//					vo.unit_id = unit_id;
//					vo.fan = fan;
//					l2.add(vo);
//					
//					gVo.ruleArr = l2;
//					l.add(gVo);
//				}else{
//					RuleVo vo = new RuleVo();
//					vo.is_mark = uMark;
//					vo.group_id = gGid;
//					vo.type_name = type_name;
//					vo.value = value;
//					vo.unit_id = unit_id;
//					vo.fan = fan;
//					
//					l.get(gGid-1).ruleArr.add(vo);
//				}
			}
			
			for(int i =0;i< l.size();i++){
				RuleGroupVo gVo = l.get(i);
				if(gVo.is_mark == 0){//大公式不是符号
					if(gVo.is_fee == 1)//用费用统计
					{
						for(int j = 0;j < gVo.ruleArr.size();j++){
							RuleVo vo = gVo.ruleArr.get(j);
							if(j == 0){
								sqlF.append(
										"\r    (" +
										"\r        select case when sum(fee) is null then 0 else sum(fee) end" +
										"\r        as count from t_org_organization_item_count_month" +
										"\r        where ");
							}
							
							if(vo.is_mark == 1){//小公式不是符号
								sqlF.append("\r        "+vo.value);
							}else{
								if(vo.fan == 1)
								{
									if(vo.type_name.equals("org_order_organization_id")){									
										sqlF.append("\r        org_order_organization_id not in " +
												"\r        (select original_id from t_sys_stat_department_vs_original_department where stat_id " +
												"\r        in("+vo.value.replaceAll(":", ",").substring(0, vo.value.length()-1)+"))");
									}else if(vo.type_name.equals("org_operate_organization_id")){
										sqlF.append("\r        org_operate_organization_id not in (select original_id " +
												"\r       from t_sys_stat_department_vs_original_department" +
												"\r       where stat_id in("+vo.value.replaceAll(":", ",").substring(0, vo.value.length()-1)+"))");
									}else if(vo.type_name.equals("org_item_id")){
										String[] strArr = vo.value.split(":");
										sqlF.append("\r        org_item_id not in (");
										for(int k =0;k< strArr.length ;k++){
											sqlF.append("'"+strArr[k]+"',");
										}
										sqlF.delete(sqlF.length()-1, sqlF.length());
										sqlF.append(")");
									}else if(vo.type_name.equals("org_item_name")){
										String[] strArr = vo.value.split(":");
										sqlF.append("\r        org_item_name not in (");
										for(int k =0;k< strArr.length ;k++){
											sqlF.append("'"+strArr[k]+"',");
										}
										sqlF.delete(sqlF.length()-1, sqlF.length());
										sqlF.append(")");
									}else if(vo.type_name.equals("org_fee_type_id")){
										String[] strArr = vo.value.split(":");
										sqlF.append("\r        org_fee_type_id not in (");
										for(int k =0;k< strArr.length ;k++){
											sqlF.append("'"+strArr[k]+"',");
										}
										sqlF.delete(sqlF.length()-1, sqlF.length());
										sqlF.append(")");
									}else if(vo.type_name.equals("sjly")){
										if(vo.value == null || vo.value.equals("")){
											System.out.println("vo.value == null");
										}else if(vo.value.equals("0")){
											sqlF.append("\r        sjly=0");
										}else if(vo.value.equals("1")){
											sqlF.append("\r        sjly=1");
										}
									}else if(vo.type_name.equals("org_order_doctor_id")){
										String[] strArr = vo.value.split(":");
										sqlF.append("\r        org_order_doctor_id not in (");
										for(int k =0;k< strArr.length ;k++){
											sqlF.append("'"+strArr[k]+"',");
										}
										sqlF.delete(sqlF.length()-1, sqlF.length());
										sqlF.append(")");
									}else if(vo.type_name.equals("org_operate_doctor_id")){
										String[] strArr = vo.value.split(":");
										sqlF.append("\r        org_operate_doctor_id not in (");
										for(int k =0;k< strArr.length ;k++){
											sqlF.append("'"+strArr[k]+"',");
										}
										sqlF.delete(sqlF.length()-1, sqlF.length());
										sqlF.append(")");
									}
								}
								else
								{
									if(vo.type_name.equals("org_order_organization_id")){									
										sqlF.append("\r        org_order_organization_id in " +
												"\r        (select original_id from t_sys_stat_department_vs_original_department where stat_id " +
												"\r        in("+vo.value.replaceAll(":", ",").substring(0, vo.value.length()-1)+"))");
									}else if(vo.type_name.equals("org_operate_organization_id")){
										sqlF.append("\r        org_operate_organization_id in (select original_id " +
												"\r       from t_sys_stat_department_vs_original_department" +
												"\r       where stat_id in("+vo.value.replaceAll(":", ",").substring(0, vo.value.length()-1)+"))");
									}else if(vo.type_name.equals("org_item_id")){
										String[] strArr = vo.value.split(":");
										sqlF.append("\r        org_item_id in (");
										for(int k =0;k< strArr.length ;k++){
											sqlF.append("'"+strArr[k]+"',");
										}
										sqlF.delete(sqlF.length()-1, sqlF.length());
										sqlF.append(")");
									}else if(vo.type_name.equals("org_item_name")){
										String[] strArr = vo.value.split(":");
										sqlF.append("\r        org_item_name in (");
										for(int k =0;k< strArr.length ;k++){
											sqlF.append("'"+strArr[k]+"',");
										}
										sqlF.delete(sqlF.length()-1, sqlF.length());
										sqlF.append(")");
									}else if(vo.type_name.equals("org_fee_type_id")){
										String[] strArr = vo.value.split(":");
										sqlF.append("\r        org_fee_type_id in (");
										for(int k =0;k< strArr.length ;k++){
											sqlF.append("'"+strArr[k]+"',");
										}
										sqlF.delete(sqlF.length()-1, sqlF.length());
										sqlF.append(")");
									}else if(vo.type_name.equals("sjly")){
										if(vo.value == null || vo.value.equals("")){
											System.out.println("vo.value == null");
										}else if(vo.value.equals("0")){
											sqlF.append("\r        sjly=0");
										}else if(vo.value.equals("1")){
											sqlF.append("\r        sjly=1");
										}
									}else if(vo.type_name.equals("org_order_doctor_id")){
										String[] strArr = vo.value.split(":");
										sqlF.append("\r        org_order_doctor_id in (");
										for(int k =0;k< strArr.length ;k++){
											sqlF.append("'"+strArr[k]+"',");
										}
										sqlF.delete(sqlF.length()-1, sqlF.length());
										sqlF.append(")");
									}else if(vo.type_name.equals("org_operate_doctor_id")){
										String[] strArr = vo.value.split(":");
										sqlF.append("\r        org_operate_doctor_id in (");
										for(int k =0;k< strArr.length ;k++){
											sqlF.append("'"+strArr[k]+"',");
										}
										sqlF.delete(sqlF.length()-1, sqlF.length());
										sqlF.append(")");
									}
								}
								
							}
							
							
							if(j == gVo.ruleArr.size()-1){
								sqlF.append(
									"\r    and stat_time >= dbo.GetPeriodDate(@stat_time,@start_time)" +
									"\r    and stat_time <  dbo.GetPeriodDate(@stat_time,@end_time)" +
									"\r    )" );
							}	
						}
					
					}
					else if(gVo.is_fee == 2)//数量
					{
						for(int j = 0;j < gVo.ruleArr.size();j++){
							RuleVo vo = gVo.ruleArr.get(j);
							if(j == 0){
								sqlF.append(
										"\r    (" +
										"\r        select case when sum(org_item_count) is null then 0 else sum(org_item_count) end" +
										"\r        as count from t_org_organization_item_count_month" +
										"\r        where ");
							}
							
							if(vo.is_mark == 1){//小公式不是符号
								sqlF.append("\r        "+vo.value);
							}else{
								if(vo.fan == 1)
								{
									if(vo.type_name.equals("org_order_organization_id")){									
										sqlF.append("\r        org_order_organization_id not in " +
												"\r        (select original_id from t_sys_stat_department_vs_original_department where stat_id " +
												"\r        in("+vo.value.replaceAll(":", ",").substring(0, vo.value.length()-1)+"))");
									}else if(vo.type_name.equals("org_operate_organization_id")){
										sqlF.append("\r        org_operate_organization_id not in (select original_id " +
												"\r       from t_sys_stat_department_vs_original_department" +
												"\r       where stat_id in("+vo.value.replaceAll(":", ",").substring(0, vo.value.length()-1)+"))");
									}else if(vo.type_name.equals("org_item_id")){
										String[] strArr = vo.value.split(":");
										sqlF.append("\r        org_item_id not in (");
										for(int k =0;k< strArr.length ;k++){
											sqlF.append("'"+strArr[k]+"',");
										}
										sqlF.delete(sqlF.length()-1, sqlF.length());
										sqlF.append(")");
									}else if(vo.type_name.equals("org_item_name")){
										String[] strArr = vo.value.split(":");
										sqlF.append("\r        org_item_name not in (");
										for(int k =0;k< strArr.length ;k++){
											sqlF.append("'"+strArr[k]+"',");
										}
										sqlF.delete(sqlF.length()-1, sqlF.length());
										sqlF.append(")");
									}else if(vo.type_name.equals("org_fee_type_id")){
										String[] strArr = vo.value.split(":");
										sqlF.append("\r        org_fee_type_id not in (");
										for(int k =0;k< strArr.length ;k++){
											sqlF.append("'"+strArr[k]+"',");
										}
										sqlF.delete(sqlF.length()-1, sqlF.length());
										sqlF.append(")");
									}else if(vo.type_name.equals("sjly")){
										if(vo.value == null || vo.value.equals("")){
											System.out.println("vo.value == null");
										}else if(vo.value.equals("0")){
											sqlF.append("\r        sjly=0");
										}else if(vo.value.equals("1")){
											sqlF.append("\r        sjly=1");
										}
									}else if(vo.type_name.equals("org_order_doctor_id")){
										String[] strArr = vo.value.split(":");
										sqlF.append("\r        org_order_doctor_id not in (");
										for(int k =0;k< strArr.length ;k++){
											sqlF.append("'"+strArr[k]+"',");
										}
										sqlF.delete(sqlF.length()-1, sqlF.length());
										sqlF.append(")");
									}else if(vo.type_name.equals("org_operate_doctor_id")){
										String[] strArr = vo.value.split(":");
										sqlF.append("\r        org_operate_doctor_id not in (");
										for(int k =0;k< strArr.length ;k++){
											sqlF.append("'"+strArr[k]+"',");
										}
										sqlF.delete(sqlF.length()-1, sqlF.length());
										sqlF.append(")");
									}
								}
								else
								{
									if(vo.type_name.equals("org_order_organization_id")){									
										sqlF.append("\r        org_order_organization_id in " +
												"\r        (select original_id from t_sys_stat_department_vs_original_department where stat_id " +
												"\r        in("+vo.value.replaceAll(":", ",").substring(0, vo.value.length()-1)+"))");
									}else if(vo.type_name.equals("org_operate_organization_id")){
										sqlF.append("\r        org_operate_organization_id in (select original_id " +
												"\r       from t_sys_stat_department_vs_original_department" +
												"\r       where stat_id in("+vo.value.replaceAll(":", ",").substring(0, vo.value.length()-1)+"))");
									}else if(vo.type_name.equals("org_item_id")){
										String[] strArr = vo.value.split(":");
										sqlF.append("\r        org_item_id in (");
										for(int k =0;k< strArr.length ;k++){
											sqlF.append("'"+strArr[k]+"',");
										}
										sqlF.delete(sqlF.length()-1, sqlF.length());
										sqlF.append(")");
									}else if(vo.type_name.equals("org_item_name")){
										String[] strArr = vo.value.split(":");
										sqlF.append("\r        org_item_name in (");
										for(int k =0;k< strArr.length ;k++){
											sqlF.append("'"+strArr[k]+"',");
										}
										sqlF.delete(sqlF.length()-1, sqlF.length());
										sqlF.append(")");
									}else if(vo.type_name.equals("org_fee_type_id")){
										String[] strArr = vo.value.split(":");
										sqlF.append("\r        org_fee_type_id in (");
										for(int k =0;k< strArr.length ;k++){
											sqlF.append("'"+strArr[k]+"',");
										}
										sqlF.delete(sqlF.length()-1, sqlF.length());
										sqlF.append(")");
									}else if(vo.type_name.equals("sjly")){
										if(vo.value == null || vo.value.equals("")){
											System.out.println("vo.value == null");
										}else if(vo.value.equals("0")){
											sqlF.append("\r        sjly=0");
										}else if(vo.value.equals("1")){
											sqlF.append("\r        sjly=1");
										}
									}else if(vo.type_name.equals("org_order_doctor_id")){
										String[] strArr = vo.value.split(":");
										sqlF.append("\r        org_order_doctor_id in (");
										for(int k =0;k< strArr.length ;k++){
											sqlF.append("'"+strArr[k]+"',");
										}
										sqlF.delete(sqlF.length()-1, sqlF.length());
										sqlF.append(")");
									}else if(vo.type_name.equals("org_operate_doctor_id")){
										String[] strArr = vo.value.split(":");
										sqlF.append("\r        org_operate_doctor_id in (");
										for(int k =0;k< strArr.length ;k++){
											sqlF.append("'"+strArr[k]+"',");
										}
										sqlF.delete(sqlF.length()-1, sqlF.length());
										sqlF.append(")");
									}
								}
								
							}
							
							
							if(j == gVo.ruleArr.size()-1){
								sqlF.append(
									"\r    and stat_time >= dbo.GetPeriodDate(@stat_time,@start_time)" +
									"\r    and stat_time <  dbo.GetPeriodDate(@stat_time,@end_time)" +
									"\r    )" );
							}	
						}
					
					}
					else//用项目的点数
					{
						for(int j = 0;j < gVo.ruleArr.size();j++){
							RuleVo vo = gVo.ruleArr.get(j);
							if(j == 0){
								sqlF.append(
										"\r    (" +
										"\r    select case when sum(a.count * b.point) is not null then sum(a.count * b.point) else 0 end from " +
										"\r        (" +
										"\r        select org_item_id,sum(org_item_count) as count from t_org_organization_item_count_month" +
										"\r        where ");
							}
							
							if(vo.is_mark == 1){
								sqlF.append("\r        "+vo.value);
							}else{
								if(vo.fan == 1)
								{
									if(vo.type_name.equals("org_order_organization_id")){
										
										sqlF.append("\r        org_order_organization_id not in " +
												"\r        (select original_id from t_sys_stat_department_vs_original_department where stat_id " +
												"\r        in("+vo.value.replaceAll(":", ",").substring(0, vo.value.length()-1)+"))");
									}else if(vo.type_name.equals("org_operate_organization_id")){
										sqlF.append("\r        org_operate_organization_id not in (select original_id " +
												"\r       from t_sys_stat_department_vs_original_department" +
												"\r       where stat_id in("+vo.value.replaceAll(":", ",").substring(0, vo.value.length()-1)+"))");
									}else if(vo.type_name.equals("org_item_id")){
										String[] strArr = vo.value.split(":");
										sqlF.append("\r        org_item_id not in (");
										for(int k =0;k< strArr.length ;k++){
											sqlF.append("'"+strArr[k]+"',");
										}
										sqlF.delete(sqlF.length()-1, sqlF.length());
										sqlF.append(")");
									}else if(vo.type_name.equals("org_item_name")){
										String[] strArr = vo.value.split(":");
										sqlF.append("\r        org_item_name not in (");
										for(int k =0;k< strArr.length ;k++){
											sqlF.append("'"+strArr[k]+"',");
										}
										sqlF.delete(sqlF.length()-1, sqlF.length());
										sqlF.append(")");
									}else if(vo.type_name.equals("org_fee_type_id")){
										String[] strArr = vo.value.split(":");
										sqlF.append("\r        org_fee_type_id not in (");
										for(int k =0;k< strArr.length ;k++){
											sqlF.append("'"+strArr[k]+"',");
										}
										sqlF.delete(sqlF.length()-1, sqlF.length());
										sqlF.append(")");
									}else if(vo.type_name.equals("sjly")){
										if(vo.value == null || vo.value.equals("")){
											System.out.println("vo.value == null");
										}else if(vo.value.equals("0")){
											sqlF.append("\r        sjly=0");
										}else if(vo.value.equals("1")){
											sqlF.append("\r        sjly=1");
										}
									}else if(vo.type_name.equals("org_order_doctor_id")){
										String[] strArr = vo.value.split(":");
										sqlF.append("\r        org_order_doctor_id not in (");
										for(int k =0;k< strArr.length ;k++){
											sqlF.append("'"+strArr[k]+"',");
										}
										sqlF.delete(sqlF.length()-1, sqlF.length());
										sqlF.append(")");
									}else if(vo.type_name.equals("org_operate_doctor_id")){
										String[] strArr = vo.value.split(":");
										sqlF.append("\r        org_operate_doctor_id not in (");
										for(int k =0;k< strArr.length ;k++){
											sqlF.append("'"+strArr[k]+"',");
										}
										sqlF.delete(sqlF.length()-1, sqlF.length());
										sqlF.append(")");
									}
								}
								else
								{
									if(vo.type_name.equals("org_order_organization_id")){
										
										sqlF.append("\r        org_order_organization_id in " +
												"\r        (select original_id from t_sys_stat_department_vs_original_department where stat_id " +
												"\r        in("+vo.value.replaceAll(":", ",").substring(0, vo.value.length()-1)+"))");
									}else if(vo.type_name.equals("org_operate_organization_id")){
										sqlF.append("\r        org_operate_organization_id in (select original_id " +
												"\r       from t_sys_stat_department_vs_original_department" +
												"\r       where stat_id in("+vo.value.replaceAll(":", ",").substring(0, vo.value.length()-1)+"))");
									}else if(vo.type_name.equals("org_item_id")){
										String[] strArr = vo.value.split(":");
										sqlF.append("\r        org_item_id in (");
										for(int k =0;k< strArr.length ;k++){
											sqlF.append("'"+strArr[k]+"',");
										}
										sqlF.delete(sqlF.length()-1, sqlF.length());
										sqlF.append(")");
									}else if(vo.type_name.equals("org_item_name")){
										String[] strArr = vo.value.split(":");
										sqlF.append("\r        org_item_name in (");
										for(int k =0;k< strArr.length ;k++){
											sqlF.append("'"+strArr[k]+"',");
										}
										sqlF.delete(sqlF.length()-1, sqlF.length());
										sqlF.append(")");
									}else if(vo.type_name.equals("org_fee_type_id")){
										String[] strArr = vo.value.split(":");
										sqlF.append("\r        org_fee_type_id in (");
										for(int k =0;k< strArr.length ;k++){
											sqlF.append("'"+strArr[k]+"',");
										}
										sqlF.delete(sqlF.length()-1, sqlF.length());
										sqlF.append(")");
									}else if(vo.type_name.equals("sjly")){
										if(vo.value == null || vo.value.equals("")){
											System.out.println("vo.value == null");
										}else if(vo.value.equals("0")){
											sqlF.append("\r        sjly=0");
										}else if(vo.value.equals("1")){
											sqlF.append("\r        sjly=1");
										}
									}else if(vo.type_name.equals("org_order_doctor_id")){
										String[] strArr = vo.value.split(":");
										sqlF.append("\r        org_order_doctor_id in (");
										for(int k =0;k< strArr.length ;k++){
											sqlF.append("'"+strArr[k]+"',");
										}
										sqlF.delete(sqlF.length()-1, sqlF.length());
										sqlF.append(")");
									}else if(vo.type_name.equals("org_operate_doctor_id")){
										String[] strArr = vo.value.split(":");
										sqlF.append("\r        org_operate_doctor_id in (");
										for(int k =0;k< strArr.length ;k++){
											sqlF.append("'"+strArr[k]+"',");
										}
										sqlF.delete(sqlF.length()-1, sqlF.length());
										sqlF.append(")");
									}
								}
								
							}
							
							
							if(j == gVo.ruleArr.size()-1){
								sqlF.append(
									"\r    and stat_time >= dbo.GetPeriodDate(@stat_time,@start_time)" +
									"\r    and stat_time <  dbo.GetPeriodDate(@stat_time,@end_time)" +
									"\r    group by org_item_id" +
									"\r    ) as a," +
									"\r    (" +
									"\r        select organization_id,item_id,"+(gVo.order_operate==1?"order_point":"operate_point")+" as point,is_important,duty_id " +
									"\r        from UNIT_PRICE_ITEM_INCOME_VS_ORGANIZATION where organization_id = @organization_id" );
								if(key == 1){
									sqlF.append(
											"\r        and is_important = 1"
									);
											
								}
								sqlF.append(
										"\r        and duty_id = @duty_id" +
										"\r    ) as b" +
										"\r    where a.org_item_id = b.item_id" +
										"\r    )");
							}	
						}
					}					
				}else{
					sqlF.append("\r        "+gVo.name);
				}
			}
			sqlF.append("\r    return @rt;");
			sqlF.append("\rEND");
			String s = sqlF.toString();
			
			System.out.println(s.replaceAll("\r", "  "));
			updateData(conn,sqlF.toString());
		}catch(Exception ex){
			ex.printStackTrace();
		}		
	
	}

	
}
